﻿Option Explicit On
Option Strict On

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports DBUtilitiesLibrary
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class DARSWebservice
    Inherits System.Web.Services.WebService

    Private objDB As DARSDBConnection
    Private ds As DataSet
    Private dt As DataTable
    Private sql As String
    Private usern As String
    Private pass As String

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' TABLE OF CONTENTS
    ''
    ''  - Student
    ''  - Advisor
    ''  - Major
    ''  - Requirement
    ''  - Appointment
    ''  - Path
    ''  - User
    ''  - Course
    ''  - Pre/CoRequisite
    ''  - Email recovery
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Student 
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad
    '''<summary>
    ''' getStudentPersonalInfo
    ''' 'Input: StudentID
    ''' return the specified srtudent's personal information as dataset
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentPersonalInfo(ByVal StudentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT* FROM Student WHERE StudentID='" & StudentID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    '''<summary>
    ''' getAllStudents
    ''' Returns a DataSet containing all student records, including StudentID and StudentName
    ''' Input: n/a
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getAllStudents() As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT StudentID, StdFName + ' ' + StdLName FROM Student"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Jawad
    '''<summary>
    ''' getStudentCourseRecord
    '''Returns a DataSet containing all courses that a student has completed, including the semester taken and grade earned.
    ''' Input: StudentID.
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentCourseRecord(ByVal StudentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * FROM StudentCourse WHERE StudentID='" & StudentID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ''' <summary>
    ''' getStudentMajors
    ''' Returns a DataSet containing all majors(/minors) that a student has on record
    ''' Input: StudentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentMajors(ByVal StudentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT StudentID, MajorID, DegreeID, PathID, isPrimary, MajDegID = MajorID + ' ' + DegreeID From StudentMajor WHERE StudentID='" & StudentID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Ryan
    ''' <summary>
    ''' changeStudentType
    ''' Changes a student's StudentType
    ''' Input: StudentID, StudentTypeID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub changeStudentType(ByVal StudentID As String, ByVal TypeID As String)
        objDB = New DARSDBConnection
        sql = "UPDATE Student Set StudentTypeID='" & TypeID & "' WHERE StudentID='" & StudentID & "'"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' createStudent
    ''' Creates a Student record
    ''' Input: StudentID, LastName, FirstName, Address, City, State, 
    ''' ... ZipCode, Email, StudentTypeID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createStudent(ByVal StudentID As String, ByVal LastName As String, ByVal FirstName As String, ByVal Address As String, ByVal City As String, ByVal State As String, ByVal ZipCode As String, ByVal Email As String, ByVal StudentTypeID As String)
        objDB = New DARSDBConnection
        sql = "INSERT INTO Student (StudentID, LastName, FirstName, Address, City, State, ZipCode, Email, StudentTypeID) " & _
            "Values ('" & StudentID & "', '" & LastName & "', '" & FirstName & "', '" & Address & "', '" & City & "', '" & State & "', '" & ZipCode & "', '" & Email & "'," & StudentTypeID & ")"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' deleteStudent
    ''' Deletes a Student record
    ''' Input: StudentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteStudent(ByVal StudentID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From Student WHERE StudentID='" & StudentID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' addStudentMajor
    ''' Adds a Student's major
    ''' Input: StudentID, MajorID, DegreeID, PathID, isPrimary
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addStudentMajor(ByVal StudentID As String, ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer, ByVal isPrimary As Boolean)
        objDB = New DARSDBConnection
        sql = "INSERT Into StudentMajor (StudentID, MajorID, DegreeID, PathID, isPrimary) Values " & _
            "('" & StudentID & "', '" & MajorID & "', '" & DegreeID & "', " & PathID & ", " & _
            isPrimary.ToString & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' removeStudentMajor
    ''' Remove's a Student's major
    ''' Input: StudentID, MajorID, DegreeID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub removeStudentMajor(ByVal StudentID As String, ByVal MajorID As String, ByVal DegreeID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From StudentMajor WHERE StudentID='" & StudentID & "' AND MajorID='" & MajorID & _
            "' AND DegreeID='" & DegreeID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' addCourseToStudentRoster
    ''' Adds a single course to a student's roster (No Grade/Semester)
    ''' Input: StudentID, CourseID, AttemptNumber
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addCourseToStudentRoster(ByVal StudentID As String, ByVal CourseID As String, ByVal AttemptNumber As Integer)
        objDB = New DARSDBConnection
        sql = "INSERT Into StudentCourse (StudentID, CourseID, AttemptNumber) " & _
            "Values ('" & StudentID & "', '" & CourseID & "', " & AttemptNumber & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' addGradedCourseToStudentRoster
    ''' Adds a single course to a student's roster (Has a Grade/Semester)
    ''' Input: StudentID, CourseID, AttemptNumber, SemesterTaken, GradeEarned
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addGradedCourseToStudentRoster(ByVal StudentID As String, ByVal CourseID As String, ByVal AttemptNumber As Integer, ByVal SemesterTaken As String, ByVal GradeEarned As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into StudentCourse (StudentID, CourseID, AttemptNumber, SemesterTaken, GradeEarned) " & _
            "Values ('" & StudentID & "', '" & CourseID & "', " & AttemptNumber & ", '" & _
            SemesterTaken & "', '" & GradeEarned & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' removeCourseFromStudentRoster
    ''' Removes a single course from a student's roster
    ''' Input: StudentID, CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub removeCourseFromStudentRoster(ByVal StudentID As String, ByVal CourseID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From StudentCourse WHERE StudentID='" & StudentID & "' AND CourseID='" & CourseID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' getStudentCourseBySemester
    ''' Returns a DataSet containing a list of courses a student took in a specified semester
    ''' Input: StudentID, SemesterTaken
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentCourseBySemester(ByVal StudentID As String, ByVal SemesterTaken As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * From StudentCourse WHERE StudentID='" & StudentID & "' AND SemesterTaken='" & SemesterTaken & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Ryan
    ''' <summary>
    ''' getStudentIncompleteCourses
    ''' Returns a DataSet containing a list of courses a student has on their roster but do not contain grades/semester
    ''' Input: StudentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentIncompleteCourses(ByVal StudentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * From StudentCourse WHERE StudentID='" & StudentID & "' AND " & _
            "SemesterTaken IS NULL AND GradeEarned IS NULL"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Ryan
    ''' <summary>
    ''' getStudentFailedCourses
    ''' Returns a DataSet containing a list of courses a student has failed (D+ or lower)
    ''' Input: StudentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentFailedCourses(ByVal StudentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * From StudentCourse WHERE StudentID='" & StudentID & "' AND " & _
            "(GradeEarned='D+' OR GradeEarned='D' OR GradeEarned='D-' OR GradeEarned='F')"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Advisor 
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ''' <summary>
    ''' createAdvisor
    ''' Creates an Advisor record
    ''' Input: AdvisorID, LastName, FirstName, Email, DepartmentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createAdvisor(ByVal AdvisorID As String, ByVal LastName As String, ByVal FirstName As String, ByVal Email As String, ByVal DepartmentID As String)
        objDB = New DARSDBConnection
        sql = "INSERT INTO Advisor (AdvisorID, LastName, FirstName, Email, DepartmentID) " & _
            "Values ('" & AdvisorID & "', '" & LastName & "', '" & FirstName & "', '" & Email & "', '" & DepartmentID & "')"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' deleteAdvisor
    ''' Deletes an Advisor record
    ''' Input: AdvisorID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteAdvisor(ByVal AdvisorID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From Advisor WHERE AdvisorID='" & AdvisorID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Jawad
    '''<summary>
    ''' getDepartments
    ''' Input: collegeID
    ''' Return dataset with all departments of the specified college
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Function getDepartments(ByVal collegeID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT DepartmentID + ' |  ' +  DepartmentName, CollegeID FROM  Department WHERE CollegeID = '" & collegeID & "'"
        objDB.getDataSet(sql)
        Return ds
    End Function


    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Major
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad
    ''' <summary>
    ''' getAllMajors
    ''' Returns a DataSet with the Major-Degree ID and Major Title of all available majors.
    ''' Input: n/a
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getAllMajors() As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT MajorDegree.MajorID +' | '+ MajorDegree.DegreeID, DegreeID = MajorDegree.DegreeID, MajorID = Major.MajorID, Major.MajorTitle, MajDegID = MajorDegree.MajorID + ' ' + MajorDegree.DegreeID" & _
            " FROM Degree" & _
            " INNER JOIN  MajorDegree ON Degree.DegreeID = MajorDegree.DegreeID" & _
            " INNER JOIN  Major ON MajorDegree.MajorID = Major.MajorID "
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ''' <summary>
    ''' getAllMajorPaths
    ''' Returns a DataSet with the Major-Degree-Path ID and Major Title of all available majors.
    ''' Input: n/a
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getAllMajorPaths() As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT MajorID, DegreeID, MajDegID = MajorID + ' ' + DegreeID, PathID" & _
            " FROM MajorDegreePath"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Jawad
    '''<summary>
    ''' getMajorInfo
    ''' Returns a DataSet containing Major and Degree ID, Title, and Credit requirement information for a specified Major-Degree.
    ''' Input: MajorID and return the record as dataset
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getMajorInfo(ByVal MajorID As String, ByVal degreeID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Major.MajorID, " & _
                     "Major.MajorTitle, " & _
                     "Major.DepartmentID, " & _
                     "Degree.DegreeID, " & _
                     "Degree.DegreeTitle, " & _
                     "MajorDegree.MinCreditsRequired, " & _
                     "MajorDegree.MaxCreditsRequired " & _
                     "FROM Degree INNER JOIN MajorDegree ON Degree.DegreeID = MajorDegree.DegreeID " & _
                     "INNER JOIN Major ON MajorDegree.MajorID = Major.MajorID " & _
                     "WHERE Major.MajorID='" & MajorID & "' AND Degree.DegreeID='" & degreeID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ''' <summary>
    ''' createMajor
    ''' Creates a major
    ''' Input: MajorID, MajorTitle, DepartmentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createMajor(ByVal MajorID As String, ByVal MajorTitle As String, ByVal DepartmentID As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into Major (MajorID, MajorTitle, DepartmentID) Values ('" & MajorID & _
            "', '" & MajorTitle & "', '" & DepartmentID & "')"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' deleteMajor
    ''' Deletes a major and its related Major-Degrees, Major-Degree Paths, and Major-Degree Requirements
    ''' Input: MajorID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteMajor(ByVal MajorID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From Major WHERE MajorID='" & MajorID & "'"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' createMajorDegree
    ''' Creates a major degree
    ''' Input: MajorID, DegreeID, MinCreditsRequired, MaxCreditsRequired
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createMajorDegree(ByVal MajorID As String, ByVal DegreeID As String, ByVal MinCreds As Integer, ByVal MaxCreds As Integer)
        objDB = New DARSDBConnection
        sql = "INSERT Into MajorDegree (MajorID, DegreeID, MinCreditsRequired, MaxCreditsRequired) Values ('" & _
            MajorID & "', '" & DegreeID & "', " & MinCreds & ", " & MaxCreds & ")"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' deleteMajorDegree
    ''' Deletes a major Major-Degree and its related Major-Degree Paths and Major-Degree Requirements
    ''' Input: MajorID, DegreeID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteMajorDegree(ByVal MajorID As String, ByVal DegreeID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From MajorDegree WHERE MajorID='" & MajorID & "' AND DegreeID='" & DegreeID & "'"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' addRequirementToMajorDegree
    ''' Creates a Major-Degree requirement
    ''' Input: MajorID, DegreeID, RequirementID, NumberCoursesRequired, isPrimaryMajorRequirement
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addRequirementToMajorDegree(ByVal MajorID As String, ByVal DegreeID As String, ByVal RequirementID As Integer, ByVal NumCrsReq As Integer, ByVal isPrimaryMajReq As Boolean)
        objDB = New DARSDBConnection
        sql = "INSERT Into MajorDegreeRequirement (MajorID, DegreeID, RequirementID, " & _
            "NumberCoursesRequired, isPrimaryMajorRequirement) Values ('" & MajorID & "', '" & DegreeID & _
            "', " & RequirementID & ", " & NumCrsReq & ", " & isPrimaryMajReq & ")"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' deleteRequirementFromMajorDegree
    ''' Deletes a Major-Degree Requirement
    ''' Input: MajorID, DegreeID, RequirementID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteRequirementFromMajorDegree(ByVal MajorID As String, ByVal DegreeID As String, ByVal RequirementID As Integer)
        objDB = New DARSDBConnection
        sql = "DELETE From MajorDegreeRequirement WHERE MajorID='" & MajorID & "' AND DegreeID='" & _
            DegreeID & "' AND RequirementID=" & RequirementID
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' addCourseToMajorDegreeReq
    ''' Adds a course to a Major-Degree requirement
    ''' Input: MajorID, DegreeID, RequirementID, CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addCourseToMajorDegreeReq(ByVal MajorID As String, ByVal DegreeID As String, ByVal RequirementID As Integer, ByVal CourseID As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into MajorDegreeRequirementCourse (MajorID, DegreeID, RequirementID, " & _
            "CourseID) Values ('" & MajorID & "', '" & DegreeID & "', " & RequirementID & ", '" & _
            CourseID & "')"
        objDB.doUpdate(sql)
    End Sub

    ''' <summary>
    ''' removeCourseFromMajorDegreeReq
    ''' Removes a course from a Major-Degree Requirement
    ''' Input: MajorID, DegreeID, RequirementID, CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub removeCourseFromMajorDegreeReq(ByVal MajorID As String, ByVal DegreeID As String, ByVal RequirementID As Integer, ByVal CourseID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From MajorDegreeRequirementCourse WHERE MajorID='" & MajorID & "' AND DegreeID='" & _
            DegreeID & "' AND RequirementID=" & RequirementID & " AND CourseID='" & CourseID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' addMajorUpdateRequest
    ''' Adds a Major Update request for a student
    ''' Input: StudentID, DateTime, MajorID, DegreeID, PathID, isPrimary, isReplacement, isDeletion
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addMajorUpdateRequest(ByVal StudentID As String, ByVal DateTime As DateTime, ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer, ByVal isPrimary As Boolean, ByVal isReplacement As Boolean, ByVal isDeletion As Boolean)
        objDB = New DARSDBConnection
        sql = "INSERT Into StudentMajorUpdateRequest (StudentID, RequestDate, MajorID, DegreeID, " & _
            "PathID, isPrimary, isReplacement, isDeletion) " & _
            "Values ('" & StudentID & "', " & DateTime.ToString & ", '" & MajorID & "', '" & DegreeID & _
            "', " & PathID & ", " & isPrimary & ", " & isReplacement & ", " & isDeletion & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' resolveMajorUpdateRequest
    ''' Removes a Major Update request
    ''' Input: StudentID, DateTime, MajorID, DegreeID, PathID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub resolveMajorUpdateRequest(ByVal StudentID As String, ByVal DateTime As DateTime, ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer)
        objDB = New DARSDBConnection
        sql = "INSERT Into StudentMajorUpdateRequest (StudentID, RequestDate, MajorID, DegreeID, PathID) " & _
            "Values ('" & StudentID & "', " & DateTime.ToString & ", '" & MajorID & "', '" & DegreeID & _
            "', " & PathID & ")"
        objDB.doUpdate(sql)
    End Sub




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Requirement
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad
    '''<summary>
    ''' getMajorDegreeRequirements
    ''' Returns a DataSet containing a list of requirements for a specified Major-Degree.
    ''' Input: MajorID, DegreeID
    ''' '''</summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getMajorDegreeRequirements(ByVal majorID As String, ByVal degreeID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT*" & _
              "FROM MajorDegreeRequirement " & _
              "WHERE MajorID ='" & majorID & "' And DegreeID = '" & degreeID & "'" & _
              " ORDER BY RequirementID ASC"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    '''<summary>
    ''' getRequiredCourses
    ''' Returns a DataSet containing courses for a specified Major-Degree Requirement.
    ''' Inputs: MajorID, DegreeID, RequirementID
    ''' '''</summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getRequiredCourses(ByVal majorID As String, ByVal degreeID As String, ByVal reqID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT MajDegReqID = MajorID + ' ' + DegreeID + ' ' + cast(RequirementID as varchar(10)), CourseID FROM MajorDegreeRequirementCourse WHERE MajorID='" & _
            majorID & "' AND DegreeID='" & degreeID & "' AND RequirementID='" & reqID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ' Ryan Lelache (4/1)
    ''' <summary>
    ''' getExistingCourseRequirements
    ''' Returns a DataSet containing all requirement IDs that exist for courses
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getExistingCourseRequirements() As DataSet
        Dim ds As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Requirement.RequirementID, Requirement.RequirementName From " & _
            "Requirement INNER JOIN CourseRequirement ON " & _
            "Requirement.RequirementID=CourseRequirement.RequirementID"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ' Ryan Lelache (4/1)
    ''' <summary>
    ''' getCourseRequirementCourses
    ''' Returns a DataSet containing a list information for all courses with a specified requirement ID
    ''' Input: RequirementID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getCourseRequirementCourses(ByVal reqID As String) As DataSet
        Dim ds As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Course.CourseID, Course.CourseName, Course.DepartmentID, Course.Description, " & _
            "Course.CreditsEarned From CourseRequirement INNER JOIN Course ON " & _
            "CourseRequirement.CourseID=Course.CourseID WHERE CourseRequirement.RequirementID='" & _
            reqID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ' Ryan Lelache (4/1)
    ''' <summary>
    ''' getGenEdIDs
    ''' Returns a DataSet containing all of the RequirementIDs for GenEds
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getGenEdIDs() As DataSet
        Dim ds As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT RequirementID, RequirementName From Requirement WHERE RequirementCategoryID=2"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Ryan
    ''' <summary>
    ''' createRequirement
    ''' Creates an entry in Requirement
    ''' Input: RequirementID, RequirementName, RequirementCategoryID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createRequirement(ByVal RequirementID As Integer, ByVal RequirementName As String, ByVal RequirementCategoryID As Integer)
        objDB = New DARSDBConnection
        sql = "INSERT Into Requirement (RequirementID, RequirementName, RequirementCategoryID) " & _
            "Values (" & RequirementID & ", '" & RequirementName & "', " & RequirementCategoryID & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' deleteRequirement
    ''' Deletes a specified requirement
    ''' Input: RequirementID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteRequirement(ByVal RequirementID As Integer)
        objDB = New DARSDBConnection
        sql = "DELETE From Requirement WHERE RequirementID=" & RequirementID
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' createRequirementCategory
    ''' Creates an entry in RequirementCategory
    ''' Input: RequirementCategoryID, RequirementCategoryName, 
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub createRequirementCategory(ByVal RequirementCategoryID As Integer, ByVal RequirementCategoryName As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into RequirementCategory (RequirementCategoryID, RequirementCategoryName) " & _
            "Values (" & RequirementCategoryID & ", '" & RequirementCategoryName & "')"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' deleteRequirementCategory
    ''' Deletes a specified requirement category
    ''' Input: RequirementCategoryID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteRequirementCategory(ByVal RequirementCategoryID As Integer)
        objDB = New DARSDBConnection
        sql = "DELETE From RequirementCategory WHERE RequirementCategoryID=" & RequirementCategoryID
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' getMajorDegreeReqNumberCoursesRequired
    ''' Returns the number of courses required for a specified Major-Degree Requirement
    ''' Input: MajorID, DegreeID, RequirementID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getMajorDegreeReqNumberCoursesRequired(ByVal MajorID As String, ByVal DegreeID As String, ByVal RequirementID As Integer) As Integer
        objDB = New DARSDBConnection
        sql = "SELECT NumberCoursesRequired From MajorDegreeRequirement WHERE " & _
            "MajorID='" & MajorID & "' AND DegreeID='" & DegreeID & "' AND " & _
            "RequirementID=" & RequirementID
        ds = objDB.getDataSet(sql)
        Return CInt(ds.Tables(0).Rows(0)("NumberCoursesRequired"))
    End Function




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Appointment
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ' Joe Bittner (3/18)
    '''<summary>
    ''' getAppointments
    ''' Returns a DataSet containing available appointments for a specified advisor
    ''' Input: AdvisorID
    '''</summary>
    <WebMethod()> _
    Public Function getAvailableAppointments(ByVal advisorID As String) As DataSet
        Dim status As String = "Opened"
        objDB = New DARSDBConnection
        sql = "Select AdvisorID, DateTime From Appointment Where AdvisorID = '" & advisorID & "'And Status = '" & status & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    ' Ryan Lelache (4/1)
    ''' <summary>
    ''' getStudentAppointments
    ''' Returns a DataSet containing all appointments for a specified student
    ''' Input: StudentID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getStudentAppointments(ByVal studentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Advisor.FirstName, Advisor.LastName, Appointment.AdvisorID, " & _
            "Appointment.DateTime, Appointment.Reason, Appointment.Status From Appointment " & _
            "INNER JOIN Advisor ON Appointment.AdvisorID=Advisor.AdvisorID Where StudentID ='" & studentID & "'"
        Dim ds As DataSet = objDB.getDataSet(sql)
        Return ds
    End Function

    ' Ryan Lelache (3/20)
    '''<summary>
    ''' addAppointment
    ''' Creates a new Appointment record for a specified Advisor at a given Date/Time.
    ''' Input: AdvisorID, DateTime.
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub addAppointment(ByVal advisorID As String, ByVal datetime As DateTime)
        Dim status As String = "Opened"
        objDB = New DARSDBConnection
        sql = "INSERT INTO Appointment (AdvisorID, DateTime, Status) Values ('" & advisorID & "', '" & datetime.ToString & _
            "', '" & status & "')"
        objDB.doUpdate(sql)
    End Sub

    'Ryan (4/2)
    ''' <summary>
    ''' deleteAppointment
    ''' Deletes a specified appointment
    ''' Input: AdvisorID, DateTime
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteAppointment(ByVal AdvisorID As String, ByVal DateTime As DateTime)
        objDB = New DARSDBConnection
        sql = "DELETE From Appointment WHERE AdvisorID='" & AdvisorID & "' AND DateAndTime=" & DateTime.ToString()
        objDB.doUpdate(sql)
    End Sub

    ' Joe Bittner (3/24)
    '''<summary>
    ''' updateStudentAppointment
    ''' Updates an existing Appointment with a reason and updated status.
    ''' Inputs: AdvisorID, DateTime, Reason, StudentID.
    ''' </summary>
    <WebMethod()> _
    Public Sub updateStudentAppointment(ByVal advisorID As Integer, ByVal datetime As DateTime, ByVal reason As String, ByVal StudentID As Integer)
        objDB = New DARSDBConnection
        Dim status As String = "Unconfirmed"
        sql = "Update Appointment Set StudentID ='" & StudentID & "', Reason ='" & reason & "', Status ='" & status & "' Where DateTime ='" & datetime & "' And AdvisorID ='" & advisorID & "'"
        objDB.doUpdate(sql)
    End Sub

    ' Joe Bittner 3/24
    ''' getAdvisors
    ''' Returns a DataSet containing all advisors
    ''' Input: n/a
    <WebMethod()> _
    Public Function getAdvisors() As DataSet
        objDB = New DARSDBConnection
        sql = "Select AdvisorID From Advisor"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Ryan
    ''' <summary>
    ''' changeAppointmentDateTime
    ''' Changes the date/time of a scheduled appointment
    ''' Input: AdvisorID, DateTime (ID), DateTime (New)
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub changeAppointmentDateTime(ByVal AdvisorID As String, ByVal DateTimeID As DateTime, ByVal DateTimeNew As DateTime)
        objDB = New DARSDBConnection
        sql = "UPDATE Appointment Set DateAndTime='" & DateTimeNew & "' WHERE AdvisorID='" & _
            AdvisorID & "' AND DateAndTime='" & DateTimeID & "'"
        objDB.doUpdate(sql)
    End Sub


    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Path
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad
    '''<summary>
    ''' getMajorDegreePaths
    ''' Returns a DataSet with all paths for the specified major-degree
    ''' Input: MajorID, DegreeID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getMajorDegreePaths(ByVal MajorID As String, ByVal degreeID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT* FROM MajorDegreePath WHERE MajorID='" & MajorID & "' AND DegreeID='" & degreeID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    '''<summary>
    ''' getPathRequirements
    ''' Returns a DataSet containing all of the Major-Degree requirements for a given Major-Degree Path.
    ''' Input: MajorID, DegreeID, PathID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getPathRequirements(ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer) As DataSet
        Dim ds As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT MajorDegreeRequirement_MajorID, MajorDegreeRequirement_DegreeID, MajorDegreeRequirement_RequirementID," & _
            " MajDegReqID = MajorDegreeRequirement_MajorID + ' ' + MajorDegreeRequirement_DegreeID + ' ' + cast(MajorDegreeRequirement_RequirementID as varchar(10))" & _
            " From MajorDegreePathRequirement" & _
            " WHERE MajorDegreePath_MajorID = '" & MajorID & "' AND" & _
            " MajorDegreePath_DegreeID = '" & DegreeID & "' AND" & _
            " MajorDegreePath_PathID=" & PathID
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    '''<summary>
    ''' createPath
    ''' Creates a new Path record for a given Major-Degree
    ''' Input: MajorID, DegreeID, PathName, PathNum
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function createPath(ByVal MajorID As String, ByVal DegreeID As String, ByVal PathName As String, ByVal PathNum As Integer) As Integer
        objDB = New DARSDBConnection
        sql = "INSERT INTO MajorDegreePath (MajorID, DegreeID, PathID, PathName) Values ('" & MajorID & "', '" & DegreeID & "', " & _
            PathNum & ", '" & PathName & "')"
        Return objDB.doUpdate(sql)
    End Function

    'Jawad
    '''<summary>
    '''deletePath
    '''Deletes a path and its requirements from the specified Major-Degree and returns the number of rows deleted or -1 if error occured.
    '''Input: MajorID, DegreeID, PathID.
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function deletePath(ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer) As Integer
        objDB = New DARSDBConnection
        'Removes all MDPath Requirements for the MDPath before deleting the path itself
        sql = "DELETE FROM MajorDegreePathRequirement WHERE MajorID='" & MajorID & "' AND DegreeID='" & DegreeID & "' AND PathID='" & PathID & "'"
        objDB.doUpdate(sql)
        'Removes the MDPath
        sql = "DELETE FROM MajorDegreePath WHERE (MajorID = '" & MajorID & "') AND (DegreeID = '" & DegreeID & "') AND (PathID =" & PathID & ")"
        Return objDB.doUpdate(sql)
    End Function

    'Jawad
    '''<summary>
    '''addMajorDegreeReqToPath
    '''Adds the specified major-degree-requirement to the the specified path
    '''Input: MajorID, DegreeID, PathID, ReqID.
    '''Note: The Requirement specified (ReqID) has to be existing in the table MajorDegreeRequirement for this method to work.
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function addMajorDegreeReqToPath(ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer, ByVal ReqID As Integer) As Integer
        objDB = New DARSDBConnection
        sql = "INSERT INTO MajorDegreePathRequirement" & _
                    " (MajorDegreePath_MajorID, MajorDegreePath_DegreeID, MajorDegreePath_PathID," & _
                    " MajorDegreeRequirement_MajorID, MajorDegreeRequirement_DegreeID, MajorDegreeRequirement_RequirementID)" & _
                    " VALUES ('" & MajorID & "','" & DegreeID & "','" & PathID & "','" & MajorID & "'," & DegreeID & "," & ReqID & ")"
        Return objDB.doUpdate(sql)
    End Function

    'Jawad
    '''<summary>
    '''deleteMajorDegreeReqFromPath
    '''Deletes the specified Requirement From the specified Major-Degree-Path
    '''Input: MajorID, DegreeID, PathID
    '''return the number of rows deleted or -1 if error occured
    '''</summary>
    <WebMethod(EnableSession:=True)> _
    Public Function deleteMajorDegreeReqFromPath(ByVal MajorID As String, ByVal DegreeID As String, ByVal PathID As Integer, ByVal redID As Integer) As Integer
        objDB = New DARSDBConnection
        sql = "DELETE FROM MajorDegreePathRequirement WHERE" & _
            " MajorDegreePath_MajorID = '" & MajorID & "' AND" & _
            " MajorDegreePath_DegreeID = '" & DegreeID & "' AND" & _
            "MajorDegreePath_PathID = " & PathID & " AND" & _
            "MajorDegreeRequirement_RequirementID = " & redID
        Return objDB.doUpdate(sql)
    End Function




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' User
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad / Steve
    '''<summary>
    '''Returns the a dataset with TypeID and typeName of the specified user if user is found in DB.
    '''Otherwise, it returns empty dataset (0 rows in the first table of the dataset) 
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Function getUserType(ByVal userID As String, ByVal password As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT UserTable.TypeID, Type.TypeName" & _
            " FROM UserTable" & _
            " INNER JOIN Type" & _
            " ON UserTable.TypeID = Type.TypeID" & _
            " WHERE UserTable.UserID='" & userID & "' AND UserTable.Password='" & password & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Jawad / Steve
    '''<summary>
    ''' input: UserID, usertypeid (
    '''Returns a DataSet containing the specified user's personal Information
    ''' This assumes that getUserType() was called to get the user's TypeID to be passed to it
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Function getUserPersonalInfo(userID As String, ByVal usertypeid As Integer) As DataSet
        objDB = New DARSDBConnection
        If usertypeid = 1 Then
            sql = "SELECT* FROM Student  WHERE UserID='" & userID & "'"
        ElseIf usertypeid = 2 Then
            sql = "SELECT* FROM Advisor  WHERE UserID='" & userID & "'"
        End If
        Return objDB.getDataSet(sql)
    End Function



    'Ryan
    ''' <summary>
    ''' createUser
    ''' Creates a user account with a password
    ''' Input: UserID, Password, Type
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function createUser(ByVal UserID As String, ByVal PW As String, ByVal Type As Integer) As Integer
        objDB = New DARSDBConnection
        sql = "INSERT INTO User (UserID, Password, Type) Values " & _
            "('" & UserID & "', '" & PW & "', " & Type & ")"
        Return objDB.doUpdate(sql)
    End Function

    'Ryan
    ''' <summary>
    ''' deleteUser
    ''' Deletes a user account
    ''' Input: UserID, Password
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteUser(ByVal UserID As String, ByVal PW As String)
        objDB = New DARSDBConnection
        sql = "DELETE From User WHERE UserID='" & UserID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' changeUserPassword
    ''' Changes a user's password if their entered password is correct
    ''' Input: UserID, Password(old), Password(new)
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub changeUserPassword(ByVal UserID As String, ByVal PW1 As String, ByVal PW2 As String)
        objDB = New DARSDBConnection
        Dim Result As Boolean = checkUserPassword(UserID, PW1)
        If Result Then
            sql = "UPDATE User Set Password='" & PW2 & "' WHERE UserID='" & UserID & "'"
            objDB.doUpdate(sql)
        End If
    End Sub

    'Ryan
    ''' <summary>
    ''' checkUserPassword
    ''' Validates that a submitted Password is correct
    ''' Input: UserID, Password
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function checkUserPassword(ByVal UserID As String, ByVal PW As String) As Boolean
        objDB = New DARSDBConnection
        Dim Result As Boolean
        sql = "SELECT Password From User WHERE UserID='" & UserID & "'"
        Dim ds As DataSet = objDB.getDataSet(sql)
        If String.Equals(PW, ds.Tables(0).Rows(0).Item(0)) Then
            Result = True
        Else
            Result = False
        End If
        Return Result
    End Function

    'Rob
    ''' <summary>
    ''' checkUserExists
    ''' Validates that a submitted username does not already exist
    ''' Input: UserID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function checkUserExists(ByVal UserID As String) As Boolean
        objDB = New DARSDBConnection
        Dim Result As Boolean
        sql = "SELECT UserID From User WHERE UserID='" & UserID & "'"
        Dim ds As DataSet = objDB.getDataSet(sql)
        If String.Equals(UserID, ds.Tables(0).Rows(0).Item(0)) Then
            Result = True
        Else
            Result = False
        End If
        Return Result
    End Function

    'Ryan
    ''' <summary>
    ''' assignUserToStudent
    ''' Assigns as User account to a Student
    ''' Input: StudentID, UserID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub assignUserToStudent(ByVal StudentID As String, ByVal UserID As String)
        objDB = New DARSDBConnection
        sql = "UPDATE Student Set UserID='" & UserID & "' WHERE StudentID='" & StudentID & "'"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' assignUserToAdvisor
    ''' Assigns as User account to an Advisor
    ''' Input: AdvisorID, UserID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub assignUserToAdvisor(ByVal AdvisorID As String, ByVal UserID As String)
        objDB = New DARSDBConnection
        sql = "UPDATE Student Set UserID='" & UserID & "' WHERE AdvisorID='" & AdvisorID & "'"
        objDB.doUpdate(sql)
    End Sub




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Course
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Jawad
    '''<summary>
    ''' getCourseInfo
    ''' Returns a DataSet containing the information for the specified course 
    ''' Input: CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Function getCourseInfo(ByVal courseID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * FROM Course WHERE CourseID='" & courseID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Roman Paik (4/2)
    '''<summary>
    ''' getCompletedSemesters
    ''' Retrieves list of completed semesters
    ''' Input: StudentID
    ''' Returns a dataset with ALL the semesters that the specified Student has taken classes
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Function getCompletedSemester(ByVal studentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT DISTINCT SemesterTaken FROM StudentCourse WHERE StudentID = '" & studentID & "'"
        Return objDB.getDataSet(sql)
    End Function

    'Jawad
    '''<summary>
    ''' input: studentID, semester (e.g :SP10 for spring 2010, FL08 for fall 2008)
    ''' Return dataset with courseID courseName, Grade, AttemptNumber for the courses that the specified student
    '''  has taken or is currently registered for in the specified semester
    ''' </summary>
    <WebMethod(enablesession:=True)> _
    Public Function getSemesterCourses(ByVal studentID As String, ByVal semester As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Course.CourseID, Course.CourseName,  StudentCourse.GradeEarned, StudentCourse.AttemptNumber" & _
            " FROM Course INNER JOIN StudentCourse" & _
            " ON Course.CourseID = StudentCourse.CourseID" & _
            " WHERE StudentCourse.StudentID ='" & studentID & "' AND StudentCourse.SemesterTaken = '" & semester & "'"
        ds = objDB.getDataSet(sql)

        Return ds
    End Function

    'Jihad (4/1)
    '''<summary>
    ''' getDepartmentCourses
    ''' Input: DepartmentID
    ''' Returns a DataSet containing the information for all courses for the specified department
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Function getCourses(ByVal DepartmentID As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT * FROM Course WHERE DepartmentID = '" & DepartmentID & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Jihad (4/1)
    '''<summary>
    ''' addCourse
    ''' Creates a new course
    ''' Input: CourseID, CourseName, Description, DepartmentID, EarnedCredits
    '''</summary>
    <WebMethod(enablesession:=True)> _
    Public Sub addCourse(ByVal CourseID As String, ByVal CourseName As String, ByVal Description As String, ByVal DepartmentID As String, ByVal EarnedCredits As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into Course (CourseID, CourseName, Description, DepartmentID, CreditsEarned) " & _
            "Values ('" & CourseID & "','" & CourseName & "','" & Description & "','" & DepartmentID & "'," & EarnedCredits & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' assignRequirementToCourse
    ''' Assigns a specified Requirement to a Course
    ''' Input: RequirementID, CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub assignRequirementToCourse(ByVal RequirementID As Integer, ByVal CourseID As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into CourseRequirement (CourseID, RequirementID) Values " & _
            "('" & CourseID & "', " & RequirementID & ")"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' removeRequirementFromCourse
    ''' Removes a specified Requirement from a Course
    ''' Input: RequirementID, CourseID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub removeRequirementFromCourse(ByVal RequirementID As Integer, ByVal CourseID As String)
        objDB = New DARSDBConnection
        sql = "DELETE From CourseRequirement Where CourseID='" & CourseID & "' AND RequirementID=" & RequirementID
        objDB.doUpdate(sql)
    End Sub




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '' Pre/CoRequisite 
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Ryan
    ''' <summary>
    ''' makeCoursePreRequisite
    ''' Creates a PreRequisite entry for two courses
    ''' Input: CourseID, PreReqID (CourseID)
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub makeCoursePreRequisite(ByVal CourseID As String, ByVal PreReqID As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into PreCoRequisite (CourseID, PreCoRequisiteID, IsPrerequisite) Values " & _
            "('" & CourseID & "', '" & PreReqID & "', True)"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' makeCourseCoRequisite
    ''' Creates a CoRequisite entry for two courses
    ''' Input: CourseID, CoReqID (CourseID)
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub makeCourseCoRequisite(ByVal CourseID As String, ByVal CoReqID As String)
        objDB = New DARSDBConnection
        sql = "INSERT Into PreCoRequisite (CourseID, PreCoRequisiteID, IsPrerequisite) Values " & _
            "('" & CourseID & "', '" & CoReqID & "', False)"
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' makePreCoReqCombo
    ''' Creates a ComboPreRequisite entry
    ''' Input: ID1, ID2, NumberThatArePreCoReq, isAnd
    ''' Note: If you are using one PreCoRequisiteID and one ComboID,
    '''    then place the PCRID first and ComboID second
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub makePreCoReqCombo(ByVal ID1 As Integer, ByVal ID2 As Integer, ByVal NumberThatArePreCoReq As Integer, ByVal isAnd As Boolean)
        objDB = New DARSDBConnection

        Select Case NumberThatArePreCoReq
            Case 0
                sql = "INSERT Into ComboPreCoRequisite (ComboID1, ComboID2, IsAnd) Values" & _
                    "(" & ID1 & ", " & ID2 & ", " & isAnd.ToString & ")"
            Case 1
                sql = "INSERT Into ComboPreCoRequisite (PreCoRequisiteID1, ComboID1, IsAnd) Values" & _
                    "(" & ID1 & ", " & ID2 & ", " & isAnd.ToString & ")"
            Case 2
                sql = "INSERT Into ComboPreCoRequisite (PreCoRequisiteID1, PreCoRequisiteID2, IsAnd) Values" & _
                    "(" & ID1 & ", " & ID2 & ", " & isAnd.ToString & ")"
            Case Else
                sql = ""
        End Select

        objDB.doUpdate(sql)

    End Sub

    'Ryan
    ''' <summary>
    ''' deletePreCoRequisite
    ''' Deletes a PreCoRequisite
    ''' Input: PreCoRequisiteID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deletePreCoRequisite(ByVal PreCoRequisiteID As Integer)
        objDB = New DARSDBConnection
        sql = "DELETE From PreCoRequisite WHERE PreCoRequisiteID=" & PreCoRequisiteID
        objDB.doUpdate(sql)
    End Sub

    'Ryan
    ''' <summary>
    ''' deleteComboPreCoRequisite
    ''' Deletes a ComboPreCoRequisite
    ''' Input: ComboID
    ''' </summary>
    <WebMethod(EnableSession:=True)> _
    Public Sub deleteComboPreCoRequisite(ByVal ComboID As Integer)
        objDB = New DARSDBConnection
        sql = "DELETE From ComboPreCoRequisite WHERE ComboID=" & ComboID
        objDB.doUpdate(sql)
    End Sub


    'Jawad
    
    ''' <summary>
    ''' retrieves UserID for the specified email address
    ''' </summary>
    ''' <param name="email">string : email to be retrieved</param>
    ''' <returns>UserID if match found, empty string if no match found</returns>
    <WebMethod(enablesession:=True)> _
    Public Function findUser(ByVal email As String) As DataSet

        objDB = New DARSDBConnection
        sql = "SELECT UserID, firstName, LastName FROM" & _
                    " (SELECT UserID,Email, FirstName, LastName FROM Student " & _
                      " UNION " & _
                      " SELECT UserID, Email, FirstName, LastName FROM Advisor)" & _
                      " AS T" & _
                      " WHERE Email='" & email & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function

    'Jawad
    ''' <summary>
    ''' Retrive the security questions and answers for the specified user
    ''' </summary>
    ''' <param name="userId">string: the UserID</param>
    ''' <returns>A dataset with the 2 security questions and corresponding answers</returns>

    <WebMethod(enablesession:=True)> _
    Public Function getSecurityQA(ByVal userId As String) As DataSet
        objDB = New DARSDBConnection
        sql = "SELECT Password, SecurityQ1, SecurityA1, SecurityQ2, SecurityA2" & _
            " FROM UserTable " & _
            " WHERE UserID='" & userId & "'"
        ds = objDB.getDataSet(sql)
        Return ds
    End Function
End Class